--Average Net Income and Number of Districts and Classification (is_top_1_percent) by
--district_net_income (total_revenue - total_expenses)
WITH client_transactions_2020_ranked
AS
(
WITH client_transactions_2020
AS
(
SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade,
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume,
payment_qty
FROM clients c
LEFT JOIN transactions t
ON c.nces_district_id = t.nces_district_id
WHERE YEAR(client_add_date) <= '2020'
AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
AND YEAR(payment_date) = '2020'
)
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank,
CASE
WHEN rank <= 12 THEN 'TRUE'
ELSE 'FALSE'
END AS is_top_1_percent,
client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level,
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM
(
SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id)
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade,
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM client_transactions_2020
ORDER BY net_payment_volume DESC
)
)
, finances_revised
AS
(
WITH finances_r
AS
(
SELECT nces_district_id,
CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
total_revenue,
total_expenses,
local_revenue,
load_date
FROM finances
WHERE total_revenue IS NOT NULL
AND total_expenses IS NOT NULL
AND local_revenue IS NOT NULL
)
SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
FROM
(
SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
FROM finances_r
WHERE YEAR(load_date) >= report_year_fall
GROUP BY nces_district_id
) a
LEFT JOIN finances_r b
ON a.nces_district_id = b.nces_district_id
AND a.most_recent_report_year = b.report_year_fall
WHERE a.most_recent_report_year IS NOT NULL
AND b.total_revenue IS NOT NULL
AND b.total_expenses IS NOT NULL
AND b.local_revenue IS NOT NULL
)
SELECT total_revenue - total_expenses as net_income, is_top_1_percent, AVG(net_payment_volume) as
average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f
ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, net_income
ORDER BY is_top_1_percent DESC;